﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ScriptGenerator
{
    public class SQLGenerator
    {
        private IRepository _repository;
        private StringBuilder _sbScript;
        private List<string> _tableNames;
        
        public SQLGenerator(IRepository repository)
        {
            _repository = repository;
            _sbScript = new StringBuilder();

            // Populate all tablenames
            _tableNames = _repository.GetAllTableNames();
        }
        public SQLGenerator(IRepository repository, string table)
        {
            _repository = repository;
            _sbScript = new StringBuilder();

            // Populate all tablenames
            _tableNames = _repository.GetAllTableNames();
            for (int cntr = _tableNames.Count - 1; cntr >= 0;cntr--)
            {
                if (_tableNames[cntr] != table)
                {
                    _tableNames.Remove(_tableNames[cntr]);
                }
            }
        }

        public string GenerateTables()
        {
            //Console.WriteLine("Generating the tables....");
            _tableNames.ForEach(delegate(string tableName)
            {
                List<Column> columns = _repository.GetColumnsFromTable(tableName);

                if (columns.Count > 0)
                {
                    _sbScript.AppendFormat("CREATE TABLE [{0}] (", tableName);

                    columns.ForEach(delegate(Column col)
                    {
                        switch (col.DataType)
                        {
                            case "varchar":
                                //Modified length 255 to 4000  due to size limit - raised by John C
                                //Also modified nchar to nvarchar for fixed length issue
                                if (col.CharacterMaxLength <= 4000 && col.CharacterMaxLength != -1)
                                {
                                    _sbScript.AppendFormat("[{0}] {1}({2}) {3} {4} {5}, "
                                        , col.ColumnName
                                        , "nvarchar"
                                        , col.CharacterMaxLength
                                        , (col.IsNullable == YesNoOptionEnum.YES ? "NULL" : "NOT NULL")
                                        , (!string.IsNullOrEmpty(col.ColumnDefault) ? "DEFAULT " + col.ColumnDefault : string.Empty)
                                        , System.Environment.NewLine);
                                }
                                else
                                {
                                    _sbScript.AppendFormat("[{0}] {1} {2} {3}, "
                                       , col.ColumnName
                                       , "ntext"
                                       , (col.IsNullable == YesNoOptionEnum.YES ? "NULL" : "NOT NULL")
                                       , System.Environment.NewLine);
                                }
                                break;
                            case "nvarchar":
                                //Modified length 255 to 4000  due to size limit - raised by John C
                                if (col.CharacterMaxLength <= 4000 && col.CharacterMaxLength != -1)
                                {
                                    _sbScript.AppendFormat("[{0}] {1}({2}) {3} {4} {5}, "
                                        , col.ColumnName
                                        , "nvarchar"
                                        , col.CharacterMaxLength
                                        , (col.IsNullable == YesNoOptionEnum.YES ? "NULL" : "NOT NULL")
                                        , (!string.IsNullOrEmpty(col.ColumnDefault) ? "DEFAULT " + col.ColumnDefault : string.Empty)
                                        , System.Environment.NewLine);
                                }
                                else
                                {
                                    _sbScript.AppendFormat("[{0}] {1} {2} {3}, "
                                       , col.ColumnName
                                       , "ntext"
                                       , (col.IsNullable == YesNoOptionEnum.YES ? "NULL" : "NOT NULL")                                       
                                       , System.Environment.NewLine);
                                }
                                break;
                            case "char":
                                _sbScript.AppendFormat("[{0}] {1}({2}) {3} {4} {5}, "
                                    , col.ColumnName
                                    , "nchar"
                                    , col.CharacterMaxLength
                                    , (col.IsNullable == YesNoOptionEnum.YES ? "NULL" : "NOT NULL")
                                    , (!string.IsNullOrEmpty(col.ColumnDefault) ? "DEFAULT " + col.ColumnDefault : string.Empty)
                                    , System.Environment.NewLine);
                                break;
                            case "smalldatetime":
                                _sbScript.AppendFormat("[{0}] {1} {2} {3} {4}, "
                                    , col.ColumnName
                                    , "datetime"
                                    , (col.IsNullable == YesNoOptionEnum.YES ? "NULL" : "NOT NULL")
                                    , (!string.IsNullOrEmpty(col.ColumnDefault) ? "DEFAULT " + col.ColumnDefault : string.Empty)
                                    , System.Environment.NewLine);
                                break;
                            default:
                                if (col.NumericPrecision > 0 && col.NumericScale > 0 && col.DataType.ToLower().IndexOf("date") < 0)
                                {
                                    _sbScript.AppendFormat("[{0}] {1}({2},{3}) {4} {5} {6} {7}, "
                                        , col.ColumnName
                                        , col.DataType
                                        , col.NumericPrecision
                                        , col.NumericScale
                                        , (col.IsIdentity ? "IDENTITY(" + col.SeedValue.ToString() + "," + col.IncrementValue.ToString() + ")" : string.Empty)
                                        , (col.IsNullable == YesNoOptionEnum.YES ? "NULL" : "NOT NULL")
                                        , (!string.IsNullOrEmpty(col.ColumnDefault) ? "DEFAULT " + col.ColumnDefault : string.Empty)
                                        , System.Environment.NewLine);
                                }
                                else
                                {
                                    _sbScript.AppendFormat("[{0}] {1} {2} {3} {4} {5}, "
                                        , col.ColumnName
                                        , col.DataType
                                        , (col.IsIdentity ? "IDENTITY(" + col.SeedValue.ToString() + "," + col.IncrementValue.ToString() + ")" : string.Empty)
                                        , (col.IsNullable == YesNoOptionEnum.YES ? "NULL" : "NOT NULL")
                                        , (!string.IsNullOrEmpty(col.ColumnDefault) ? "DEFAULT " + col.ColumnDefault : string.Empty)
                                        , System.Environment.NewLine);
                                }
                                break;
                        }   
                    });

                    // Remove the last comma
                    _sbScript.Remove(_sbScript.Length - 2, 2);
                    _sbScript.AppendFormat(");{0}", System.Environment.NewLine);
                }
            });

            return _sbScript.ToString();
        }
        public string GenerateTableContent()
        {
            //Console.WriteLine("Generating the data....");
            ///TODO
            ///IDENTITY RESET
            _tableNames.ForEach(delegate(string tableName)
            {
                DataTable dt = _repository.GetDataFromTable(tableName);

                _sbScript.Append("SET IDENTITY_INSERT [" + tableName + "] ON;");
                _sbScript.Append(System.Environment.NewLine);

                string scriptPrefix = GetInsertScriptPrefix(tableName, dt);
                
                for (int iRow = 0; iRow < dt.Rows.Count; iRow++)
                {
                    _sbScript.Append(scriptPrefix);
                    for (int iColumn = 0; iColumn < dt.Columns.Count; iColumn++)
                    {
                        if (dt.Rows[iRow][iColumn] == System.DBNull.Value)
                            _sbScript.Append("null");
                        else if (dt.Columns[iColumn].DataType.FullName == "System.Byte[]")
                        {
                            //ADDED as recomended by "Digitalbeach"
                            byte[] byteData = (byte[])dt.Rows[iRow][iColumn];
                            if (byteData.Length > 0)
                            {
                                string byteDataStr = "0x" + BitConverter.ToString(byteData).Replace("-", string.Empty);
                                _sbScript.Append(byteDataStr);
                            }
                            else
                            {
                                _sbScript.Append("CAST('' AS VARBINARY)");
                            }
                        }  
                        else
                            _sbScript.AppendFormat("'{0}'", dt.Rows[iRow][iColumn].ToString().Replace("'", "''")
                                .Replace(";", Convert.ToString((char)192))
                                .Replace("\\", Convert.ToString((char)193)));

                        _sbScript.Append(iColumn != dt.Columns.Count - 1 ? "," : "");
                    }
                    _sbScript.Append(");");
                    _sbScript.Append(System.Environment.NewLine);                    
                }

                _sbScript.Append("SET IDENTITY_INSERT [" + tableName + "] OFF;");
                _sbScript.Append(System.Environment.NewLine);
            });            

            return _sbScript.ToString();
        }        
        public string GeneratePrimaryKeys()
        {
            //Console.WriteLine("Generating the primary keys....");
            _tableNames.ForEach(delegate(string tableName)
            {
                List<Constraint> primaryKeys = _repository.GetPrimaryKeysFromTable(tableName);

                if (primaryKeys.Count > 0)
                {
                    _sbScript.AppendFormat("ALTER TABLE [{0}] ADD CONSTRAINT [{1}] PRIMARY KEY (", tableName, primaryKeys.FirstOrDefault().ConstraintName);

                    primaryKeys.ForEach(delegate(Constraint constraint)
                    {
                        _sbScript.AppendFormat("[{0}]", constraint.ColumnName);
                        _sbScript.Append(",");
                    });

                    // Remove the last comma
                    _sbScript.Remove(_sbScript.Length - 1, 1);
                    _sbScript.AppendFormat(");{0}", System.Environment.NewLine);
                }
            });

            return _sbScript.ToString();
        }
        public string GenerateUniqueKeys()
        {
            //Console.WriteLine("Generating the primary keys....");
            _tableNames.ForEach(delegate(string tableName)
            {
                List<Constraint> uniqueKeys = _repository.GetUniqueKeysFromTable(tableName);

                if (uniqueKeys.Count > 0)
                {
                    IEnumerable<string> ukList = uniqueKeys.Select(u => u.ConstraintName).Distinct();
                    foreach (string uk in ukList)
                    {
                        _sbScript.AppendFormat("ALTER TABLE [{0}] ADD CONSTRAINT [{1}] UNIQUE NONCLUSTERED (", tableName, uk);

                        uniqueKeys.FindAll(u => u.ConstraintName == uk).ForEach(delegate(Constraint constraint)
                        {
                            _sbScript.AppendFormat("[{0}]", constraint.ColumnName);
                            _sbScript.Append(",");
                        });

                        // Remove the last comma
                        _sbScript.Remove(_sbScript.Length - 1, 1);
                        _sbScript.AppendFormat(");{0}", System.Environment.NewLine);
                    }
                }
            });

            return _sbScript.ToString();
        }
        public string GenerateForeignKeys()
        {
            //Console.WriteLine("Generating the foreign keys....");
            List<Constraint> foreignKeys = _repository.GetAllForeignKeys();
            _tableNames.ForEach(delegate(string tableName)
            {
                IEnumerable<string> fkList = foreignKeys.FindAll(j=>j.ConstraintTableName == tableName).Select(i => i.ConstraintName).Distinct();
                foreach (string fk in fkList)
                {
                    var fkRows = from f in foreignKeys
                                 where f.ConstraintName == fk && f.ConstraintTableName.Length > 0
                                 orderby f.OrdinalPosition
                                 select f;
                    var refRows = from u in foreignKeys
                                  where u.ConstraintName == fk && u.UniqueConstraintTableName.Length > 0
                                  orderby u.OrdinalPosition
                                  select u;

                    if (fkRows.Count<Constraint>() > 0 && refRows.Count<Constraint>() > 0)
                    {
                        _sbScript.AppendFormat("ALTER TABLE [{0}] ADD CONSTRAINT [{1}] FOREIGN KEY (",tableName,fk);
                        foreach(Constraint fkCons in fkRows)
                        {
                            _sbScript.AppendFormat("[{0}]", fkCons.ColumnName);
                            _sbScript.Append(",");
                        }
                        _sbScript.Remove(_sbScript.Length - 1, 1);
                        _sbScript.AppendFormat(") REFERENCES [{0}] (", refRows.First().UniqueConstraintTableName);
                        foreach (Constraint refCons in refRows)
                        {
                            _sbScript.AppendFormat("[{0}]", refCons.UniqueColumnName);
                            _sbScript.Append(",");
                        }
                        _sbScript.Remove(_sbScript.Length - 1, 1);
                        _sbScript.AppendFormat(");{0}", System.Environment.NewLine);
                    }
                }
            });
            //foreignKeys.ForEach(delegate(Constraint constraint)
            //{
            //    _sbScript.AppendFormat("ALTER TABLE [{0}] ADD CONSTRAINT [{1}] FOREIGN KEY ([{2}]) REFERENCES [{3}]([{4}]);{5}"
            //        , constraint.ConstraintTableName
            //        , constraint.ConstraintName
            //        , constraint.ColumnName
            //        , constraint.UniqueConstraintTableName
            //        , constraint.UniqueColumnName
            //        , System.Environment.NewLine);
            //});

            return _sbScript.ToString();
        }
        public string GenerateIndex()
        {
            ////Console.WriteLine("Generating the indexes....");
            _tableNames.ForEach(delegate(string tableName)
            {
                List<Index> tableIndexes = _repository.GetIndexesFromTable(tableName);

                if (tableIndexes.Count > 0)
                {
                    IEnumerable<string> uniqueIndexNameList = tableIndexes.Select(i => i.IndexName).Distinct();

                    foreach (string uniqueIndexName in uniqueIndexNameList)
                    {
                        var indexesByName = from i in tableIndexes
                                            where i.IndexName == uniqueIndexName
                                            orderby i.OrdinalPosition
                                            select i;

                        _sbScript.Append("CREATE ");

                        // Just get the first one to decide whether it's unique and/or clustered index
                        Index idx = indexesByName.First<Index>();
                        if (idx.Unique)
                            _sbScript.Append("UNIQUE ");
                        if (idx.Clustered)
                            _sbScript.Append("CLUSTERED ");

                        _sbScript.AppendFormat("INDEX [{0}] ON [{1}] (", idx.IndexName, idx.TableName);

                        foreach (Index col in indexesByName)
                        {
                            _sbScript.AppendFormat("[{0}] {1},", col.ColumnName, col.SortOrder.ToString());
                        }

                        // Remove the last comma
                        _sbScript.Remove(_sbScript.Length - 1, 1);
                        _sbScript.AppendLine(");");
                    }
                }
            });

            return _sbScript.ToString();
        }
        public string GeneratedScript
        {
            get { return _sbScript.ToString(); }
        }
        private string GetInsertScriptPrefix(string tableName, DataTable dt)
        {
            StringBuilder sbScriptTemplate = new StringBuilder(1000);
            sbScriptTemplate.AppendFormat("Insert Into [{0}] (", tableName);

            // Generate the field names first
            for (int iColumn = 0; iColumn < dt.Columns.Count; iColumn++)
            {
                sbScriptTemplate.AppendFormat("[{0}]{1}", dt.Columns[iColumn].ColumnName, (iColumn != dt.Columns.Count - 1 ? "," : ""));
            }

            sbScriptTemplate.AppendFormat(") Values (", tableName);
            return sbScriptTemplate.ToString();
        }
    }
}
